package com.yanqu.road.dataHelper.action.excel;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.yanqu.road.entity.annualreport.YearlyReportData;
import com.yanqu.road.dataHelper.gameServer.GameServer;
import com.yanqu.road.dataHelper.manager.ConfigMgr;
import com.yanqu.road.dataHelper.repair.BaseRepair;
import com.yanqu.road.entity.activity.unionwar.cross.UnionWarPatronsData;
import com.yanqu.road.entity.banquet.BanquetGiftInfo;
import com.yanqu.road.entity.config.goods.GoodsInfo;
import com.yanqu.road.entity.enums.eActivityType;
import com.yanqu.road.entity.enums.eMarriageStatusType;
import com.yanqu.road.entity.enums.ePlayerAttrType;
import com.yanqu.road.entity.enums.eUnionNoticeType;
import com.yanqu.road.entity.player.UserInfo;
import com.yanqu.road.utils.date.DateHelper;
import com.yanqu.road.utils.property.Property;
import com.yanqu.road.utils.property.PropertyHelper;
import com.yanqu.road.utils.string.StringUtils;

import java.math.BigInteger;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.MessageFormat;
import java.util.*;

/**
 * @author hgd
 */
public class YearlyReportLogic extends BaseRepair {

    /**
     * 登录天数<用户id,登录天数>
     */
    Map<Long, Long> loginDayMap = new HashMap<>();

    /**
     * 门客群英获胜信息<角色id,门客id,获胜次数>
     */
    Map<Long,Map<Integer,Integer>> unionWarPatronsMap = new HashMap<>();

    /**
     * 跨服冲榜活动Id集合
     */
    List<Integer> crossRankActivityIds = new ArrayList<>();

    public YearlyReportLogic(GameServer gameServer) {
        super(gameServer);
    }

    @Override
    public void repair(long param1, String param2) {

    }

    public void go() {
        // 数据范围条件
        // 结束时间
        String dateEnd = "2022-05-11 23:59:59";

        // 查询所有满足条件的用户
        List<Long> userIdList = getAllUser(dateEnd);
        // 登录天数<用户id,登录天数>
        getAllUserLoginDay(userIdList);

        // 获取群英门客信息
        getUnionWarPatronsWinInfo();

        ActivityBestRankLogic activityBestRankLogic = new ActivityBestRankLogic();
        // 跨服冲榜活动
        crossRankActivityIds = activityBestRankLogic.getActivityIdsByType(eActivityType.CrossRankActivity.getValue());

        userIdList.forEach(userId -> {
            // 年报表数据
            YearlyReportData data = new YearlyReportData();

            // 角色Id
            data.setUserId(userId);
            // -----------------角色基本信息 start-----------------
            // 角色基本信息
            UserInfo userInfo = getUserInfo(String.valueOf(data.getUserId()));
            // 区服ID
            data.setServerId(userInfo.getOriginalServerId());
            // 角色昵称
            data.getItem().setUserName(userInfo.getNickName());
            // 角色创建时间
            data.getItem().setRegisterTime(userInfo.getRegisterTime());
            // 角色最后登录时间
            data.getItem().setLastLoginTime(userInfo.getLastLoginTime());
            // 身份
            data.getItem().setTitleId(userInfo.getTitleId());

            // 角色历史最高属性
            getUserHistoryAttrInfo(data);
            // 登录天数
            data.getItem().setLoginDays(getUserLoginDay(data.getUserId()));
            // -----------------角色基本信息 end-------------------

            // ----------------商铺 start----------------
            // 建筑信息
            getBuildingInfo(data);
            // 医馆信息
            getClinicInfo(data);
            // 客栈信息(烹饪)
            getUserCookInfo(data);
            // 酒肆信息
            getUserWineShopInfo(data);
            // 庄园产量
            getManorHarvestInfo(data);
            // ----------------商铺 end------------------

            // ----------------挚友&谈心 start----------------
            // 挚友(美女)信息
            getBeautyInfo(data);
            // ----------------挚友&谈心 end------------------

            // -----------------世交&联姻&徒弟 start-----------------
            // 联姻信息
            getMarriageInfo(data);
            // 与自己联姻次数最多的世交是X玩家名称
            data.getItem().setMaxMarriageUserName(getUserInfo(String.valueOf(data.getItem().getMaxMarriageUserId())).getNickName());
            // 书信信息
            getLetterInfo(data);
            // 与自己书信来往次数最多的人是X玩家(昵称)
            data.getItem().setMaxLetterUserName(getUserInfo(String.valueOf(data.getItem().getMaxLetterUserId())).getNickName());
            // -----------------世交&联姻&徒弟 end-------------------

            // ----------------门客&商战 start----------------
            // 门客信息
            getPatronsInfo(data);
            // 群英获胜信息
            getMaxWinningPatrons(data);
            // 秦淮五艳
            // 秦淮五艳ID
            List<Long> qinHuangPatronsIdList = Arrays.asList(102415L, 102425L, 102435L, 102445L, 102455L);
            getQinHuangInfo(data, qinHuangPatronsIdList);

            // 使用道具信息
            // 门客贴
            data.getItem().setTotalCostPatronsCount(getUseItemCount(data.getUserId(), 110011L));
            // 资质丹
            data.getItem().setTotalCostTalentCount(getUseItemCount(data.getUserId(), 110005L));
            // ----------------门客&商战 end------------------

            // ----------------商会 start----------------
            // 砍价信息
            getBargainInfo(data);
            // 商会日志信息
            getUnionLogInfo(data);
            // 商会登录信息
            getUnionLoginInfo(data);
            // 整个商会里登录次数最多的人是X玩家昵称
            data.getItem().setMaxUnionLoginUserName(getUserInfo(String.valueOf(data.getItem().getMaxUnionLoginUserId())).getNickName());

            // 据点战
            // 使用乾坤一掷
            data.getItem().setTotalCostQianKunYiZhiCount(getUseItemCount(data.getUserId(), 120080L));
            // ----------------商会 end------------------

            // ----------------点赞&捣蛋&发言 start----------------
            // 点赞、捣蛋信息
            getLikeInfo(data);
            // 被点赞、被捣蛋信息
            getLikedInfo(data);
            // ----------------点赞&捣蛋&发言 end------------------

            // ----------------称号&榜单 start----------------
            // 称号信息
            getDecorationInfo(data);

            // 排行榜信息
            // 跨服冲榜
            getCrossRankInfo(data);
            // 限时冲榜
            getRankInfo(data);
            // ----------------称号&榜单 end------------------

            // ----------------宴会 start----------------
            // 宴会信息
            getBanquetInfo(data);
            // 赴宴信息
            getBanquetMemberInfo(data);
            // 跨服宴会
            getCrossBanquetInfo(data);
            // 跨服赴宴信息
            getCrossBanquetMemberInfo(data);
            // ----------------宴会 end------------------

            // 保存数据
            addData(data);
        });
    }

    /**
     * 群英获胜信息
     * @param data
     */
    private void getMaxWinningPatrons(YearlyReportData data) {
        if (!unionWarPatronsMap.containsKey(data.getUserId())) {
            return;
        }
        Map<Integer, Integer> patronsWinCountMap = unionWarPatronsMap.get(data.getUserId());
        if (patronsWinCountMap == null || patronsWinCountMap.isEmpty()) {
            return;
        }
        // 获胜最多的次数
        int maxWinCount = 0;
        // 群英里获胜次数最多的门客是X
        long maxWinningPatronsId = 0;
        for (Map.Entry<Integer, Integer> entry : patronsWinCountMap.entrySet()) {
            if (entry.getValue() > maxWinCount) {
                maxWinCount = entry.getValue();
                maxWinningPatronsId = entry.getKey();
            }
        }
        data.getItem().setMaxWinningPatronsCount(maxWinCount);
        data.getItem().setMaxWinningPatronsId(maxWinningPatronsId);
    }

    /**
     * 获取门客群英获胜信息
     */
    private void getUnionWarPatronsWinInfo() {
        PreparedStatement call = null;
        String sql = "select patronsData from db_bussinessroad_crosscenter.t_c_activity_unionwar_detail ";

        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                String patronsData = rs.getString("patronsData");
                if(!StringUtils.isNullOrEmpty(patronsData)){
                    List<UnionWarPatronsData> patronsDataList = JSON.parseObject(patronsData, new TypeReference<List<UnionWarPatronsData>>() {});
                    if(null != patronsDataList) {
                        patronsDataList.forEach(data -> {
                            long userId = data.getUserId();
                            int patronsId = data.getPatronsId();
                            int winTimes = data.getWinTimes();
                            Map<Integer, Integer> map = unionWarPatronsMap.getOrDefault(userId, new HashMap<>());
                            map.put(patronsId, map.getOrDefault(patronsId, 0) + winTimes);
                            unionWarPatronsMap.put(userId, map);
                        });
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 查询所有满足条件的用户
     */
    private List<Long> getAllUser(String dateEnd) {
        PreparedStatement call = null;
        String sql = "SELECT u.userId FROM t_u_user u where DATEDIFF(?,FROM_UNIXTIME(u.registerTime,'%Y-%m-%d'))>=7";

        List<Long> userIdList = new ArrayList<>();
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, dateEnd);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                long userId = rs.getLong("userId");
                userIdList.add(userId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
        return userIdList;
    }

    private void getAllUserLoginDay(List<Long> userIdList) {
        PreparedStatement call = null;
//        int tableIndex = (int) (userId % 16);
        String sql = "select * from t_u_attr_? where userId =? and attrId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            for (Long userId : userIdList) {
                int tableIndex = (int) (userId % 16);
                int i = 1;
                call.setInt(i++, tableIndex);
                call.setLong(i++, userId);
                // 登录天数
                call.setInt(i++, ePlayerAttrType.LoginTotalDays.getValue());
                ResultSet rs = call.executeQuery();
                while (rs.next()) {
                    loginDayMap.put(userId, rs.getLong("value"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 角色基本信息
     */
    private void getUserBaseInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_user where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                // 区服ID
                data.setServerId(rs.getInt("originalServerId"));
                // 角色昵称
                data.getItem().setUserName(rs.getString("nickName"));
                // 角色创建时间
                data.getItem().setRegisterTime(rs.getLong("registerTime"));
                // 角色最后登录时间
                data.getItem().setLastLoginTime(rs.getLong("lastLoginTime"));
                // 身份
                data.getItem().setTitleId(rs.getInt("titleId"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 角色登录天数
     */
    private Long getUserLoginDay(long userId) {
        if (loginDayMap.containsKey(userId)) {
            return loginDayMap.get(userId);
        }
        PreparedStatement call = null;
        int tableIndex = (int) (userId % 16);
        String sql = MessageFormat.format("select * from t_u_attr_{0} where userId =? and attrId = ? ", tableIndex);
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, userId + "");
            call.setInt(i++, ePlayerAttrType.LoginTotalDays.getValue());
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                loginDayMap.put(userId, rs.getLong("value"));
                return rs.getLong("value");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
        return 0L;
    }

    /**
     * 角色历史最高属性
     */
    private void getUserHistoryAttrInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_history_attribute where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                // 最高赚速
                data.getItem().setTopEarnSpeed(new BigInteger(rs.getString("earnSpeed")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 建筑信息
     */
    private void getBuildingInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_building where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            int buildingCount = 0;
            int totalStaffCount = 0;
            while (rs.next()) {
                buildingCount++;
                totalStaffCount += rs.getInt("staffCount");
            }
            // 解锁了X个类型的商铺
            data.getItem().setBuildingCount(buildingCount);
            // 所有商铺累计招募的总伙计数X
            data.getItem().setTotalStaffCount(totalStaffCount);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 医馆信息
     */
    private void getClinicInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_clinic_data where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                // 医馆累计接诊X个病人
                data.getItem().setTreatPatientCount(rs.getInt("curedPatientCount"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 烹饪信息
     */
    private void getUserCookInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_inn_cook where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                // 客栈累计烹饪X道菜
                data.getItem().setTotalCookTimes(rs.getInt("totalCookTimes"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 酒肆信息
     */
    private void getUserWineShopInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_wineshop_data where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                // 酒肆累计接待X个客人
                data.getItem().setTotalReceiveCustomers(rs.getInt("receptionBeautyCount"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 庄园产量记录
     */
    private void getManorHarvestInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT yield FROM t_l_manor_harvest where userId = ?";
        try {
            call = getGameServer().getLogConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 庄园累计产量X
            BigInteger totalYield = BigInteger.ZERO;
            while (rs.next()) {
                BigInteger yield = new BigInteger(rs.getString("yield"));
                totalYield = totalYield.add(yield);
            }
            data.getItem().setTotalYield(totalYield);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 美女信息
     */
    private void getBeautyInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_beauty where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 美女数量
            int beautyCount = 0;

            // 亲密度最高的美女Id
            int maxIntimacyBeautyId = 0;
            // 最高的亲密度
            int maxIntimacy = 0;
            // 亲密度最高的美女的获得时间
            long maxIntimacyCreateTime = 0;

            // 魅力值最高的美女Id
            int maxGlamourBeautyId = 0;
            // 最高的魅力值
            int maxGlamour = 0;
            // 魅力值最高的美女的获得时间
            long maxGlamourCreateTime = 0;

            // 累计谈心(传唤)X次
            int totalTrystTimes = 0;
            // 最高的谈心(传唤)次数
            int maxTrystTimes = 0;
            // 谈心最多的挚友是X
            int maxTrystBeautyId = 0;
            // 和谈心最多的挚友共同培养了X个爱徒
            int maxTrystChildCount = 0;
            while (rs.next()) {
                beautyCount++;
                // 总亲密度
                int totalIntimacy = rs.getInt("intimacy") + rs.getInt("skinIntimacy");
                if (totalIntimacy >= maxIntimacy) {
                    // 相同时取最早获得的
                    if (totalIntimacy != 0 && maxIntimacyCreateTime != 0 && totalIntimacy == maxIntimacy && rs.getLong("createTime") > maxIntimacyCreateTime) {
                        continue;
                    }
                    maxIntimacy = totalIntimacy;
                    maxIntimacyBeautyId = rs.getInt("beautyId");
                    maxIntimacyCreateTime = rs.getLong("createTime");
                }
                // 总魅力值
                int totalGlamour = rs.getInt("glamour") + rs.getInt("skinGlamour");
                if (totalGlamour >= maxGlamour) {
                    // 相同时取最早获得的
                    if (totalGlamour != 0 && maxGlamourCreateTime != 0 && totalGlamour == maxGlamour && rs.getLong("createTime") > maxGlamourCreateTime) {
                        continue;
                    }
                    maxGlamour = totalGlamour;
                    maxGlamourBeautyId = rs.getInt("beautyId");
                    maxGlamourCreateTime = rs.getLong("createTime");
                }
                // 传唤次数
                int trystTimes = rs.getInt("totalTrystTimes") + rs.getInt("travelTimes") + rs.getInt("bathTimes");
                totalTrystTimes += trystTimes;
                if (trystTimes >= maxTrystTimes) {
                    maxTrystTimes = trystTimes;
                    maxTrystBeautyId = rs.getInt("beautyId");
                    maxTrystChildCount = rs.getInt("childCount");
                }
            }
            data.getItem().setTotalBeautyCount(beautyCount);
            data.getItem().setMaxIntimacyBeautyId(maxIntimacyBeautyId);
            data.getItem().setMaxGlamourBeautyId(maxGlamourBeautyId);
            data.getItem().setTotalTrystTimes(totalTrystTimes);
            data.getItem().setMaxTrystBeautyId(maxTrystBeautyId);
            data.getItem().setMaxTrystChildCount(maxTrystChildCount);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 联姻信息
     */
    private void getMarriageInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_marriage where userId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 联姻次数
            int marriageCount = 0;
            // 联姻次数最多的联姻Id
            long maxMarriageUserId = 0;
            // 和同一个世交联姻最多的是X次
            int maxMarriageCount = 0;
            // 培养了X个徒弟
            int adultChildCount = 0;
            // 孤独终老了X个徒弟
            int lonelyChildCount = 0;
            // 联姻计数器
            Map<Long, Integer> marriageCnt = new HashMap<>();
            while (rs.next()) {
                adultChildCount++;
                // 联姻
                if(rs.getInt("status") == eMarriageStatusType.MARRIED.getValue()) {
                    marriageCount++;
                }
                // 孤独终老
                if (rs.getInt("status") == eMarriageStatusType.LONELY.getValue()) {
                    lonelyChildCount++;
                }
                long appointUserId = rs.getLong("appointUserId");
                if (appointUserId != 0) {
                    marriageCnt.put(appointUserId, marriageCnt.getOrDefault(appointUserId, 0) + 1);
                }
            }
            for (Map.Entry<Long, Integer> entry : marriageCnt.entrySet()) {
                if (entry.getValue() >= maxMarriageCount) {
                    maxMarriageCount = entry.getValue();
                    maxMarriageUserId = entry.getKey();
                }
            }
            data.getItem().setMarriageCount(marriageCount);
            data.getItem().setMaxMarriageUserId(maxMarriageUserId);
            data.getItem().setMaxMarriageCount(maxMarriageCount);
            data.getItem().setAdultChildCount(adultChildCount);
            data.getItem().setLonelyChildCount(lonelyChildCount);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }


    /**
     * 书信信息
     */
    private void getLetterInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_relatives_letter where fromId = ? or toId = ?";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 与自己书信来往次数最多的人是X
            long maxLetterUserId = 0;
            // 与自己书信来往次数最多的人，来往书信累计X封
            int maxLetterCount = 0;
            // 书信计数器
            Map<Long, Integer> letterCnt = new HashMap<>();
            // 最晚发送书信的时间（用于判断）
            long maxTime = 0;
            // 最晚发送书信的时间戳
            long maxLetterSendTime = 0;
            while (rs.next()) {
                long fromId = rs.getLong("fromId");
                long toId = rs.getLong("toId");
                if (fromId == data.getUserId()) {
                    // 我发送的
                    letterCnt.put(toId, letterCnt.getOrDefault(toId, 0) + 1);
                    int createTime = rs.getInt("createTime");
                    int zeroTime = DateHelper.getZeroTime(createTime);
                    if (createTime - zeroTime > maxTime) {
                        maxTime = createTime - zeroTime;
                        maxLetterSendTime = createTime;
                    }
                }
                if (toId == data.getUserId()) {
                    letterCnt.put(fromId, letterCnt.getOrDefault(fromId, 0) + 1);
                }
            }
            for (Map.Entry<Long, Integer> entry : letterCnt.entrySet()) {
                if (entry.getValue() >= maxLetterCount) {
                    maxLetterCount = entry.getValue();
                    maxLetterUserId = entry.getKey();
                }
            }
            data.getItem().setMaxLetterUserId(maxLetterUserId);
            data.getItem().setMaxLetterCount(maxLetterCount);
            data.getItem().setMaxLetterSendTime(maxLetterSendTime);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }


    /**
     * 门客信息
     */
    private void getPatronsInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_patrons where userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 培养了X个门客
            int patronCount = 0;
            // 赚速最高的门客是X(Id)
            long maxEarningsPatronsId = 0;
            // 赚速最高的门客赚速是X
            BigInteger maxEarningsPatronsSpeed = BigInteger.ZERO;
            while (rs.next()) {
                patronCount++;
                long patronsId = rs.getLong("patronsId");
                BigInteger patronsSpeed = new BigInteger(rs.getString("ability"));
                if (patronsSpeed.compareTo(maxEarningsPatronsSpeed) > 0) {
                    maxEarningsPatronsId = patronsId;
                    maxEarningsPatronsSpeed = patronsSpeed;
                }

            }
            data.getItem().setTotalPatronsCount(patronCount);
            data.getItem().setMaxEarningsPatronsId(maxEarningsPatronsId);
            data.getItem().setMaxEarningsPatronsSpeed(maxEarningsPatronsSpeed);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 秦淮五艳信息
     */
    private void getQinHuangInfo(YearlyReportData data, List<Long> qinHuangPatronsIdList) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_l_get_patrons where userId = ? and patronsId in (?)";
        try {
            call = getGameServer().getLogConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            call.setString(i++, StringUtils.listToString(qinHuangPatronsIdList, ","));
            ResultSet rs = call.executeQuery();
            // 最早获得的时间
            Date earliestTime = null;
            // 最早获得的秦淮五艳门客Id
            long firstQinHuaiPatronsId = 0;
            while (rs.next()) {
                Date time = rs.getDate("time");
                if (earliestTime == null || time.before(earliestTime)) {
                    earliestTime = time;
                    firstQinHuaiPatronsId = rs.getLong("patronsId");
                }
            }
            data.getItem().setFirstQinHuaiPatronsId(firstQinHuaiPatronsId);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 使用道具信息
     */
    private BigInteger getUseItemCount(long userId, long goodsId) {
        PreparedStatement call = null;
        String sql = "SELECT num FROM t_l_goods_2 WHERE isGet = 0 AND userId = ? AND goodsId = ?";
        BigInteger count = BigInteger.ZERO;
        try {
            call = getGameServer().getLogConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, userId + "");
            call.setString(i++, goodsId + "");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                count = count.add(new BigInteger(rs.getString("num")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
        return count;
    }

    /**
     * 点赞、捣蛋信息
     */
    private void getLikeInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT u.nickName,ir.interactiveType FROM t_u_interactive_record ir LEFT JOIN t_u_user u ON ir.operateUserId = u.userId where ir.userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 捣蛋别人次数
            int hateCount = 0;
            // 点赞别人次数
            int likeCount = 0;
            // 捣蛋别人最多的人是X
            String maxHateUserName = "";
            // 点赞别人最多的人是X
            String maxLikeUserName = "";
            // 点赞次数计数器
            Map<String, Integer> likeCnt = new HashMap<>();
            // 捣蛋次数计数器
            Map<String, Integer> hateCnt = new HashMap<>();
            while (rs.next()) {
                String nickName = rs.getString("nickName");
                int interactiveType = rs.getInt("interactiveType");
                if (interactiveType == 1) {
                    likeCount++;
                    likeCnt.put(nickName, likeCnt.getOrDefault(nickName, 0) + 1);
                } else if (interactiveType == 2) {
                    hateCount++;
                    hateCnt.put(nickName, hateCnt.getOrDefault(nickName, 0) + 1);
                }
            }
            int maxHateCount = 0;
            for (Map.Entry<String, Integer> entry : hateCnt.entrySet()) {
                if (entry.getValue() >= maxHateCount) {
                    maxHateCount = entry.getValue();
                    maxHateUserName = entry.getKey();
                }
            }
            int maxLikeCount = 0;
            for (Map.Entry<String, Integer> entry : likeCnt.entrySet()) {
                if (entry.getValue() >= maxLikeCount) {
                    maxLikeCount = entry.getValue();
                    maxLikeUserName = entry.getKey();
                }
            }
            data.getItem().setTotalHateCount(hateCount);
            data.getItem().setTotalLikeCount(likeCount);
            data.getItem().setMaxHateUserName(maxHateUserName);
            data.getItem().setMaxLikeUserName(maxLikeUserName);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }


    /**
     * 被点赞、被捣蛋信息
     */
    private void getLikedInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT u.nickName,ir.interactiveType FROM t_u_interactive_record ir LEFT JOIN t_u_user u ON ir.userId = u.userId where ir.operateUserId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 被捣蛋次数
            int hatedCount = 0;
            // 被点赞次数
            int likedCount = 0;
            // 被捣蛋次数最多的人是X
            String maxHatedUserName = "";
            // 被点赞次数最多的人是X
            String maxLikedUserName = "";
            // 被点赞次数计数器
            Map<String, Integer> likedCnt = new HashMap<>();
            // 被捣蛋次数计数器
            Map<String, Integer> hatedCnt = new HashMap<>();
            while (rs.next()) {
                String nickName = rs.getString("nickName");
                int interactiveType = rs.getInt("interactiveType");
                if (interactiveType == 1) {
                    likedCount++;
                    likedCnt.put(nickName, likedCnt.getOrDefault(nickName, 0) + 1);
                } else if (interactiveType == 2) {
                    hatedCount++;
                    hatedCnt.put(nickName, hatedCnt.getOrDefault(nickName, 0) + 1);
                }
            }
            int maxHatedCount = 0;
            for (Map.Entry<String, Integer> entry : hatedCnt.entrySet()) {
                if (entry.getValue() >= maxHatedCount) {
                    maxHatedCount = entry.getValue();
                    maxHatedUserName = entry.getKey();
                }
            }
            int maxLikedCount = 0;
            for (Map.Entry<String, Integer> entry : likedCnt.entrySet()) {
                if (entry.getValue() >= maxLikedCount) {
                    maxLikedCount = entry.getValue();
                    maxLikedUserName = entry.getKey();
                }
            }
            data.getItem().setTotalHatedCount(hatedCount);
            data.getItem().setTotalLikedCount(likedCount);
            data.getItem().setMaxHatedUserName(maxHatedUserName);
            data.getItem().setMaxLikedUserName(maxLikedUserName);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }


    /**
     * 称号信息
     */
    private void getDecorationInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_decoration where userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 获得称号次数
            int decorationCount = 0;
            // 最早获得称号的时间
            long earliestTime = 0;
            // 最早获得的称号是X
            int earliestDecorationId = 0;
            // 称号记录
            Map<Integer, Long> decorationMap = new HashMap<>();
            while (rs.next()) {
                int decorationId = rs.getInt("decorationId");
                // 不是称号跳过处理
                if (!ConfigMgr.decorationLevelMap.containsKey(decorationId)) {
                    continue;
                }
                decorationCount++;
                long modifyTime = rs.getLong("modifyTime");
                decorationMap.put(decorationId, modifyTime);
            }
            for (Map.Entry<Integer, Long> entry : decorationMap.entrySet()) {
                if (earliestTime == 0 || entry.getValue() < earliestTime) {
                    earliestTime = entry.getValue();
                    earliestDecorationId = entry.getKey();
                }
            }
            data.getItem().setTotalDecorationCount(decorationCount);
            data.getItem().setEarliestDecorationId(earliestDecorationId);
            // 最大称号的ID
            int maxLevelDecorationId = getUserDecorationMaxLevel(decorationMap.keySet());
            data.getItem().setMaxDecorationId(maxLevelDecorationId);
            data.getItem().setMaxDecorationTime(decorationMap.getOrDefault(maxLevelDecorationId, 0L));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }


    /**
     * 跨服冲榜
     * @param data
     */
    private void getCrossRankInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_activity_rank where userId = ? and activityId in (?) ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            call.setString(i++, StringUtils.listToString(crossRankActivityIds, ","));
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                int activityId = rs.getInt("activityId");
                int rank = rs.getInt("rank");
                long lastUpdateTime = rs.getLong("lastUpdateTime");
                if (data.getItem().getMaxRank() <= rank && (data.getItem().getMaxRankTime() == 0 || lastUpdateTime < data.getItem().getMaxRankTime())) {
                    data.getItem().setMaxRank(rank);
                    data.getItem().setMaxRankTime(lastUpdateTime);
                    data.getItem().setBigRankTypeOfMaxRank(eActivityType.CrossRankActivity.getValue());
                    data.getItem().setRankTypeOfMaxRank(activityId);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 限时冲榜
     * @param data
     */
    private void getRankInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM t_u_rank where userId = ? and bigRankType = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            call.setString(i++, eActivityType.RankActivity.getValue() + "");
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                int type = rs.getInt("type");
                int rank = rs.getInt("rank");
                long lastUpdateTime = rs.getLong("lastUpdateTime");
                if (data.getItem().getMaxRank() <= rank && (data.getItem().getMaxRankTime() == 0 || lastUpdateTime < data.getItem().getMaxRankTime())) {
                    data.getItem().setMaxRank(rank);
                    data.getItem().setMaxRankTime(lastUpdateTime);
                    data.getItem().setBigRankTypeOfMaxRank(eActivityType.RankActivity.getValue());
                    data.getItem().setRankTypeOfMaxRank(type);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }



    /**
     * 获取最高的称号
     * @param userDecorationList
     * @return
     */
    public static int getUserDecorationMaxLevel(Set<Integer> userDecorationList) {
        int userDecorationClass = -1;    //称号越高，阶级越大
        int curDecorationId = 0;
        for (int decorationId : userDecorationList) {
            GoodsInfo goodsInfo = ConfigMgr.goodsInfoMap.get(decorationId);
            if(goodsInfo == null){
                continue;
            }
            List<Integer> list = StringUtils.stringToIntegerList(goodsInfo.getExtendParam(), "\\|");
            if(list.size() < 6){
                continue;
            }
            int tempClass = list.get(4);
            if (tempClass > userDecorationClass) {
                userDecorationClass = tempClass;
                curDecorationId = decorationId;
            }
        }
        return curDecorationId;
    }


    /**
     * 宴会信息
     */
    private void getBanquetInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_banquet_personal where userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 累计开宴次数
            int banquetCount = 0;
            while (rs.next()) {
                banquetCount++;
            }
            data.getItem().setTotalOpenBanquet(banquetCount);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 加入宴会信息
     */
    private void getBanquetMemberInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_banquet_personal_member where userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 累计赴宴次数
            int joinBanquetCount = 0;
            while (rs.next()) {
                joinBanquetCount++;
            }
            data.getItem().setTotalJoinBanquet(joinBanquetCount);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 宴会信息
     */
    private void getCrossBanquetInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_banquet where userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 累计开宴次数
            int banquetCount = 0;
            while (rs.next()) {
                banquetCount++;
            }
            data.getItem().setTotalOpenBanquet(data.getItem().getTotalOpenBanquet() + banquetCount);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 加入跨服宴会信息
     */
    private void getCrossBanquetMemberInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_banquet_member where userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 累计跨服赴宴次数
            int joinBanquetCount = 0;
            // 跨服宴会累计获得多少元宝
            BigInteger totalCrossBanquetGetIngots = BigInteger.ZERO;
            while (rs.next()) {
                joinBanquetCount++;
                totalCrossBanquetGetIngots = totalCrossBanquetGetIngots.add(getJoinReward(rs.getInt("pos"), rs.getInt("giftId")));
            }
            data.getItem().setTotalJoinCrossBanquet(joinBanquetCount);
            data.getItem().setTotalCrossBanquetGetIngots(totalCrossBanquetGetIngots);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 获取全服盛宴的对应位置的对应奖励
     */
    public static BigInteger getJoinReward(int pos,int giftId){
        String reward = "";
        BanquetGiftInfo banquetGiftInfo = ConfigMgr.banquetGiftInfoMap.get(giftId);
        if(banquetGiftInfo != null && !"0".equals(banquetGiftInfo.getJoinReward())){
            int max = -1;
            List<String> rewardList = StringUtils.stringToStringList(banquetGiftInfo.getJoinReward(),"\\|");
            for(String rewardInfo : rewardList){
                List<String> posRewardList = StringUtils.stringToStringList(rewardInfo,":");
                //最小位置以上的简历
                int paramPosMin = Integer.parseInt(posRewardList.get(0));
                if(pos >= paramPosMin && paramPosMin > max){
                    max = paramPosMin;
                    reward = posRewardList.get(1);
                }
            }
        }
        Property property = PropertyHelper.parseStringToProperty(reward);
        // 这里只获取元宝数量
        return property.getCountByGoodsId(101000);
    }




    /**
     * 砍价信息
     */
    private void getBargainInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_union_bussinessman_detail where userId = ? ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 砍价次数
            int totalBargainTimes = 0;
            // 砍价总元宝
            int totalBargainIngots = 0;
            while (rs.next()) {
                totalBargainTimes++;
                totalBargainIngots += rs.getInt("bargainPrice");
            }
            data.getItem().setTotalBargainTimes(totalBargainTimes);
            data.getItem().setTotalBargainIngots(totalBargainIngots);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }


    /**
     * 商会日志信息
     */
    private void getUnionLogInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_union_notice where unionUid = (SELECT unionUid FROM t_u_user where userId = ? ) ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 加入商会的人数（需去重）
            Set<Long> joinUnionUserIdSet = new HashSet<>();
            // 离开商会的人数（需去重）
            Set<Long> quitUnionUserIdSet = new HashSet<>();
            while (rs.next()) {
                long userId = rs.getLong("userId");
                int type = rs.getInt("type");
                // 加入或创建
                if (type == eUnionNoticeType.JoinUnion.getValue() || type == eUnionNoticeType.ConstructUnion.getValue()) {
                    joinUnionUserIdSet.add(userId);
                }
                // 离开或踢出
                if (type == eUnionNoticeType.QuitUnion.getValue() || type == eUnionNoticeType.KickMember.getValue()) {
                    quitUnionUserIdSet.add(userId);
                }
            }
            data.getItem().setTotalJoinUnion(joinUnionUserIdSet.size());
            data.getItem().setTotalQuitUnion(quitUnionUserIdSet.size());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

    /**
     * 商会日志信息
     */
    private void getUnionLoginInfo(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "SELECT * FROM db_bussinessroad_crosscenter.t_c_union_member where unionUid = (SELECT unionUid FROM t_u_user where userId = ? ) ";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setString(i++, data.getUserId() + "");
            ResultSet rs = call.executeQuery();
            // 登录天数最多的玩家id
            long maxLoginDayUserId = 0;
            // 登录天数最多的玩家登录天数
            int maxLoginDay = 0;
            while (rs.next()) {
                long userId = rs.getLong("userId");
                // 获取登录天数
                Long userLoginDay = getUserLoginDay(userId);
                if (userLoginDay != null && userLoginDay > maxLoginDay) {
                    maxLoginDay = userLoginDay.intValue();
                    maxLoginDayUserId = userId;
                }
            }
            data.getItem().setMaxUnionLoginUserId(maxLoginDayUserId);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }



    /**
     * 保存数据
     */
    protected void addData(YearlyReportData data) {
        PreparedStatement call = null;
        String sql = "insert into t_u_yearly_report(userId,serverId,item) values (?,?,?)";
        try {
            call = getGameServer().getConnection().prepareStatement(sql);
            int i = 1;
            call.setLong(i++, data.getUserId());
            call.setLong(i++, data.getServerId());
            call.setString(i++, JSON.toJSONString(data.getItem()));
            call.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            getGameServer().closeConnection(call, null);
        }
    }

}
